package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.UserInfo;
import c0907m.group2.aitsm.util.Helper;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Users table.
 */
public class UserDb extends BaseDb {
    private String insert = "INSERT INTO users (name, email, mobile, description, admin, password) VALUES (?, ?, ?, ?, ?, ?)";
    private String update = "UPDATE users SET name = ?, email = ?, mobile = ?, description = ?, active = ?, admin = ?, password = ? WHERE id = ?";

    /**
     * Insert a User into database.
     *
     * @param user row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(UserInfo user) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            st.setString(1, user.getName());
            st.setString(2, user.getEmail());
            st.setString(3, user.getMobile());
            st.setString(4, user.getDescription());
            st.setBoolean(5, user.isAdmin());
            st.setString(6, user.getPassword());
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a User into database.
     *
     * @param user row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(UserInfo user) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(user);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Users into database.
     *
     * @param users list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<UserInfo> users) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (UserInfo user : users) {
                doUpdate(user);
            }
        } finally {
            close();
        }
    }

    /**
     * Get an User for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public UserInfo select(int id) throws SQLException {
        String select = "SELECT * FROM users WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Users
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<UserInfo> select() throws SQLException {
        return select("");
    }

    /**
     * Get a list of Users for the given name or email.
     *
     * @param name the row's name to search.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<UserInfo> select(String name) throws SQLException {
        String select = "SELECT * FROM users WHERE name LIKE '%" + name + "%' OR email LIKE '%" + name + "%'";
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<UserInfo> list = new ArrayList<UserInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param user the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(UserInfo user) throws SQLException {
        st.setString(1, user.getName());
        st.setString(2, user.getEmail());
        st.setString(3, user.getMobile());
        st.setString(4, user.getDescription());
        st.setBoolean(5, user.isActive());
        st.setBoolean(6, user.isAdmin());
        st.setString(7, user.getPassword());
        st.setInt(8, user.getId());
        st.executeUpdate();
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private UserInfo getResult() throws SQLException {
        UserInfo user = new UserInfo();
        user.setId(rs.getInt(1));
        user.setName(rs.getString(2));
        user.setEmail(rs.getString(3));
        user.setPassword(rs.getString(4));
        user.setMobile(rs.getString(5));
        user.setDescription(rs.getString(6));
        user.setActive(rs.getBoolean(7));
        user.setAdmin(rs.getBoolean(8));

        return user;
    }
}
